﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DAL_Menu
/// </summary>
public class DAL_Menu
{
    DbProvider db = new DbProvider();

    public DataTable Menu_GetById(string Id)
    {
        DataTable dt = new DataTable();
        try
        {
            string sql = string.Format("select * from Menu where Id = '{0}' And [Status] = '1' Order by [Order]", Id);
            //string sql = string.Format("select * from Menu where Id = '{0}' Order by [Order]", Id);
           dt = db.ExecuteQuery(sql);
        }
        catch (Exception)
        {
            throw;
        }
        return dt;
    }
    public DataTable Menu_GetByIdAllStatus(string Id)
    {
        DataTable dt = new DataTable();
        try
        {
            //string sql = string.Format("select * from Menu where Id = '{0}' And [Status] = '1' Order by [Order]", Id);
            string sql = string.Format("select * from Menu where Id = '{0}' Order by [Order]", Id);
            dt = db.ExecuteQuery(sql);
        }
        catch (Exception)
        {
            throw;
        }
        return dt;
    }
    public DataTable Menu_GetByParentId(string Id)
    {
        DataTable dt = new DataTable();
        try
        {
            string sql = string.Format("select * from Menu where Parent_Id = '{0}' And [Status] = '1' Order by [Order]", Id);
            dt = db.ExecuteQuery(sql);
        }
        catch (Exception)
        {
            throw;
        }
        
        return dt;
    }
    public DataTable getMenuAllStatus(int location)
    {

        DataTable dt = new DataTable();
        try
        {
            //string sql = string.Format("select * from Menu where Parent_Id = '0'and Location = '{0}' And [Status] = '1' Order by [Order]", location);
            string sql = string.Format("select * from Menu where Location = '{0}' Order by [Order]", location);
            dt = db.ExecuteQuery(sql);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return dt;
    }

    public DataTable getMenu(int location)
    {
        
        DataTable dt = new DataTable();
        try
        {
           string sql = string.Format("select * from Menu where Parent_Id = '0'and Location = '{0}' And [Status] = '1' Order by [Order]", location);
            //string sql = string.Format("select * from Menu where Parent_Id = '0'and Location = '{0}' Order by [Order]", location);
            dt = db.ExecuteQuery(sql);
        }
        catch(Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return dt;
    }
    public DataTable getSubMenu(string parentId)
    {

        DataTable dt = new DataTable();
        try
        {
            string sql = string.Format("select * from Menu where Id = '{0}' And [Status] = '1' Order by [Order]", parentId);
            dt = db.ExecuteQuery(sql);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return dt;
    }
    public DataTable Menu_UpdateByAll(string Id, DTO_Menu obj)
    {
        DataTable dt = new DataTable();
        //string sql = string.Format("UPDATE [Menu] SET [Name] = '{0}',Link = '{1}',[Img] = '{2}',[Order] = '{3}',[Location] = '{4}',[Parent_Id] = '{5}',[Status] = '{6}' WHERE [Id] = '{7}'", obj.Name, obj.Link, obj.Name, obj.Img, obj.Order, obj.Location, obj.Parent_Id, obj.Status, Id);
        string sql = string.Format("UPDATE [Menu] SET [Name] = N'{0}',Link = '{1}',[Img] = '{2}',[Order] = '{3}',[Location] = '{4}',[Parent_Id] = '{5}',[Status] = '{6}' WHERE [Id] = '{7}'", obj.Name, obj.Link, obj.Img, obj.Order, obj.Location, obj.Parent_Id, obj.Status, Id);
        try
        {
            db.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Menu_Delete(string Id)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("DELETE FROM Menu WHERE Id = '{0}'", Id);

        try
        {
            db.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Menu_Insert(DTO_Menu obj)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("INSERT INTO MENU VALUES (N'{0}','{1}','{2}','{3}','{4}','{5}','{6}')",obj.Name,obj.Link,obj.Img,obj.Order,obj.Location,obj.Parent_Id,obj.Status);

        try
        {
            db.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
}